﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Collections.ObjectModel;
using HellCook.Model;

namespace HellCook.DataAccess
{
    public static class MenuDeLaSemana_AdministradorBaseDeDatos
    {
        private static string PathDataBase = "Data Source=.\\SQLEXPRESS; Initial Catalog=HellCook_DataBase; Integrated Security=true";

        public static string InsertarMenuSemanal(Collection<MenuDeLaSemana> menuSemana)
        {
            string Message = "Agregado correctamente";

            for (int Ptr = 0; Ptr < menuSemana.Count; Ptr++)
            {
                SqlConnection SQLConnection;
                SQLConnection = new SqlConnection(PathDataBase);

                SqlCommand SQLCommand = SQLConnection.CreateCommand();
                SQLCommand.CommandText = "INSERT INTO [MenuSemanal] (Dia, Division_Alimenticia, Fecha, Codigo_Receta, Estado) VALUES(@Variable_Dia, @Variable_Division_Alimenticia, @Variable_Fecha, @Variable_Codigo_Receta, @Variable_Estado)";

                SQLCommand.Parameters.AddWithValue("Variable_Dia", menuSemana[Ptr].Dia);
                SQLCommand.Parameters.AddWithValue("Variable_Division_Alimenticia", menuSemana[Ptr].Division_Alimenticia);
                SQLCommand.Parameters.AddWithValue("Variable_Fecha", menuSemana[Ptr].Fecha);
                SQLCommand.Parameters.AddWithValue("Variable_Codigo_Receta", menuSemana[Ptr].Codigo_Receta);
                SQLCommand.Parameters.AddWithValue("Variable_Estado", menuSemana[Ptr].Estado);

                try
                {
                    SQLConnection.Open();

                    int Result = SQLCommand.ExecuteNonQuery();

                    if (Result <= 0)
                    {
                        Message = "Error al insertar.";
                    }
                }
                catch (Exception exception)
                {
                    Message = exception.Message;
                }
                finally
                {
                    if (SQLConnection.State == System.Data.ConnectionState.Open)
                    {
                        SQLConnection.Close();
                    }
                }
            }
            return (Message);
        }


        public static string ActualizarMenuSemanal(Collection<MenuDeLaSemana> menuSemana)
        {
            string Message = "Se actualizo correctamente.";

            for (int Ptr = 0; Ptr < menuSemana.Count; Ptr++)
            {
                SqlConnection SQLConnection;
                SQLConnection = new SqlConnection(PathDataBase);

                SqlCommand SQLCommand = SQLConnection.CreateCommand();
                SQLCommand.CommandText = "UPDATE [MenuSemanal] SET Dia = @Variable_Dia, Division_Alimenticia = @Variable_Division_Alimenticia, Fecha = @Variable_Fecha, Codigo_Receta = @Variable_Codigo_Receta, Estado = @Variable_Estado WHERE Codigo_Plan = @Variable_Codigo_Plan";

                SQLCommand.Parameters.AddWithValue("Variable_Dia", menuSemana[Ptr].Dia);
                SQLCommand.Parameters.AddWithValue("Variable_Division_Alimenticia", menuSemana[Ptr].Division_Alimenticia);
                SQLCommand.Parameters.AddWithValue("Variable_Fecha", menuSemana[Ptr].Fecha);
                SQLCommand.Parameters.AddWithValue("Variable_Codigo_Receta", menuSemana[Ptr].Codigo_Receta);
                SQLCommand.Parameters.AddWithValue("Variable_Estado", menuSemana[Ptr].Estado);
                SQLCommand.Parameters.AddWithValue("Variable_Codigo_Plan", menuSemana[Ptr].Codigo_Plan);

                try
                {
                    SQLConnection.Open();

                    int Result = SQLCommand.ExecuteNonQuery();

                    if (Result <= 0)
                    {
                        Message = "Error al actualizar.";
                    }
                }
                catch (Exception exception)
                {
                    Message = exception.Message;
                }
                finally
                {
                    if (SQLConnection.State == System.Data.ConnectionState.Open)
                    {
                        SQLConnection.Close();
                    }
                }
            }
            return (Message);
        }

        public static Collection<MenuDeLaSemana> ObtenerMenuSemanal()
        {
            Collection<MenuDeLaSemana> menuSemana = null;
            SqlDataReader ReaderInfo = null;

            SqlConnection SQLConnection;
            SQLConnection = new SqlConnection(PathDataBase);

            SqlCommand SQLCommand = SQLConnection.CreateCommand();
            SQLCommand.CommandText = "SELECT * FROM [MenuSemanal] WHERE Estado <> 'Finalizado' ";                

            try
            {
                SQLConnection.Open();
                ReaderInfo = SQLCommand.ExecuteReader();
                menuSemana = new Collection<MenuDeLaSemana>();

                while (ReaderInfo.Read())
                {
                    MenuDeLaSemana Item = new MenuDeLaSemana();

                    Item.Codigo_Plan = Convert.ToInt16(ReaderInfo["Codigo_Plan"].ToString());
                    Item.Codigo_Receta = Convert.ToInt16(ReaderInfo["Codigo_Receta"].ToString());
                    Item.Dia = ReaderInfo["Dia"].ToString();
                    Item.Division_Alimenticia = ReaderInfo["Division_Alimenticia"].ToString();
                    Item.Estado = ReaderInfo["Estado"].ToString();
                    Item.Fecha = Convert.ToDateTime(ReaderInfo["Fecha"].ToString());

                    menuSemana.Add(Item);
                }
            }
            catch (Exception)
            {
                return (null);
            }
            finally
            {
                if (SQLConnection.State == System.Data.ConnectionState.Open)
                {
                    SQLConnection.Close();
                }
            }
            return (menuSemana);
        }


        public static string FinalizarMenuSemanal()
        {
            string Message = "Menu semanal finalizado. correctamente.";

            
            SqlConnection SQLConnection;
            SQLConnection = new SqlConnection(PathDataBase);

            SqlCommand SQLCommand = SQLConnection.CreateCommand();
            SQLCommand.CommandText = "UPDATE [MenuSemanal] SET Estado = @Variable_Estado ";
                
            SQLCommand.Parameters.AddWithValue("Variable_Estado", "Finalizado");

            try
            {
                SQLConnection.Open();
                    
                int Result = SQLCommand.ExecuteNonQuery();

                if (Result <= 0)
                {
                    Message = "Error al modificar.";
                }
                    
            }
            catch (Exception ex)
            {
                return (ex.Message);
            }
            finally
            {
                if (SQLConnection.State == System.Data.ConnectionState.Open)
                {
                    SQLConnection.Close();
                }
            }            
            return (Message);
        }
    }
}
